package com.pinusmusic.dao.impl;

import com.pinusmusic.dao.SingerDao;
import com.pinusmusic.domain.Music;
import com.pinusmusic.domain.Singer;
import com.pinusmusic.util.JDBCUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

public class SingerDaoImpl implements SingerDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    /**
     * 添加歌手
     * @param addSinger
     */
    @Override
    public boolean addSinger(Singer addSinger) {
        String sql = "insert into singer(singername, introduction, birthday, singerimg) values(?,?,?,?) ";
        int i = template.update(sql, addSinger.getSingername(),
                addSinger.getIntroduction(), addSinger.getBirthday(), addSinger.getSingerimg());
        return i!=0;
    }

    @Override
    public void deleteSingerBySid(int sid) {
        String sql = "delete from singer where sid=?";
        template.update(sql,sid);
    }

    @Override
    public boolean updateSinger(Singer updateSinger) {
        System.out.println("dao层要修改的歌手："+updateSinger);
        String sql = "update singer set birthday=?,introduction=?,singerimg=? where sid=?";
        int i = template.update(sql, updateSinger.getBirthday(), updateSinger.getIntroduction()
                , updateSinger.getSingerimg(), updateSinger.getSid());
        return i!=0;
    }

    @Override
    public Singer findSingerBySid(int sid) {
        String sql = "select * from singer where sid=?";
        return template.queryForObject(sql, new BeanPropertyRowMapper<>(Singer.class), sid);
    }

    @Override
    public List<Singer> findSingerByPage(int start, int rows, String singerName) {
        String sql = "select * from singer where 1=1";
        StringBuilder sb = new StringBuilder(sql);
        ArrayList<Object> params = new ArrayList<>();
        if (singerName != null && !singerName.trim().isEmpty()) {
            sb.append(" and singername like ?");
            params.add("%" + singerName + "%");
        }
        sb.append(" limit ?,?");
        params.add(start);
        params.add(rows);
        return template.query(sb.toString(), new BeanPropertyRowMapper<>(Singer.class), params.toArray());
    }

    @Override
    public int getTotalCount(String singerName) {
        String sql = "select count(*) from singer where 1=1";
        StringBuilder sb = new StringBuilder(sql);
        ArrayList<Object> params = new ArrayList<>();
        if (singerName != null && !singerName.trim().isEmpty()) {
            sb.append(" and singername like ?");
            params.add("%" + singerName + "%");
        }
        return template.queryForObject(sb.toString(),Integer.class,params.toArray());
    }

    @Override
    public String findPathBySid(Integer sid) {
        String sql="select singerimg from singer where sid=?";
        String imgPath=null;
        try {
            imgPath=template.queryForObject(sql,String.class,sid);
        }catch (EmptyResultDataAccessException e){
            System.out.println("路径查询失败");//如果查询结果为空或多个，则返回-1
        }
        return imgPath;
    }

    @Override
    public List<Singer> findSingersByName(int start, int rows, String name) {
        if (name!=null && !name.trim().isEmpty() && !name.equals("")){
            String sql = "select * from singer where 1=0";
            StringBuilder sb = new StringBuilder(sql);
            ArrayList<Object> params = new ArrayList<>();//存放musicName的值
            sb.append(" or singername like ?");
            params.add("%"+name+"%");
            //分页
            sb.append("limit ?,?");
            params.add(start);
            params.add(rows);
            List<Singer> singerList = template.query(sb.toString(), new BeanPropertyRowMapper<>(Singer.class), params.toArray());
            return singerList.size()==0?null:singerList;
        }
        return null;
    }

    @Override
    public int getLikeCount(String name) {
        String sql = "select count(*) from singer  where 1=0";
        StringBuilder sb = new StringBuilder(sql);
        ArrayList<Object> params = new ArrayList<>();//存放musicName的值
        if (name !=null && !name.trim().isEmpty()){
            sb.append(" or singername like ?");
            params.add("%"+name+"%");
        }
        return template.queryForObject(sb.toString(),Integer.class,params.toArray());
    }

}
